package com.cxx.purchasecharge.dal.impl;

import java.util.ArrayList;
import java.util.List;

import javax.persistence.Query;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;

import com.cxx.purchasecharge.core.model.CustomerTypeCode;
import com.cxx.purchasecharge.core.model.persistence.Customer;
import com.cxx.purchasecharge.dal.CustomerDao;
import com.cxx.purchasecharge.dal.common.GenericDaoUtils;
import com.cxx.purchasecharge.dal.common.MyGenericDaoImpl;

public class CustomerDaoImpl extends MyGenericDaoImpl<Customer, Long> implements CustomerDao
{
    public CustomerDaoImpl() {
		super(Customer.class);
	}

	private static final Logger logger = Logger.getLogger (CustomerDaoImpl.class);

    @Override
	public Customer findByShortName(String shortName) {
		String sql = "select c from Customer c where c.shortName=?1";
		Query query = getEntityManager ().createQuery (sql, Customer.class);
		query.setParameter (1, shortName);
		return CollectionUtils.isNotEmpty (query.getResultList ()) ? (Customer)query.getResultList ().get (0) : null;
	}

	@Override
	public int updateDealMoney(float dealMoney, long id) {
		String sql = "update Customer c set c.dealMoney = ?1 where c.id = ?2";
		Query query = getEntityManager ().createQuery (sql);
		query.setParameter (1, dealMoney);
		query.setParameter (2, id);
		return query.executeUpdate ();
	}

	@Override
    public Page <Customer> findByFuzzy (CustomerTypeCode typeCode, Pageable pageable, String searchKey,
                                        long signUser, boolean admin)
    {
        String pageableLog = "offSet:" + pageable.getOffset () + ",pageNumber:" + pageable.getPageNumber ()
                             + ",pageSize:" + pageable.getPageSize () + ",sort:" + pageable.getSort ();
        logger.debug ("Find paging customer by fuzzy, pageable: (" + pageableLog + "), searchkey: " + searchKey);
        String keyWord = (null == searchKey ? "" : searchKey.trim ());
        Page <Customer> customerPage;
        if (!admin && 0 != signUser)
        {
            String selectCountSql = "SELECT count(c) ";
            String fromSql = "FROM Customer c where ";
            String conditionSql = "c.userSigned = ?1 and c.typeCode = ?2 ";
            if (StringUtils.isNotBlank (searchKey))
            {
                conditionSql += "and c.id IN ";
                conditionSql += "(select c1.id from Customer c1 where " + "c1.shortName like ?3 or "
                                + "c1.zipCode like ?4 or " + "c1.companyName like ?5 or "
                                + "c1.companyIntroduction like ?6 or " + "c1.address like ?7 or "
                                + "c1.email like ?8 or " + "c1.fax like ?9 or " + "c1.fixedPhone like ?10 or "
                                + "c1.customerTrade.name like ?11 or " + "c1.customerSource.name like ?12 or "
                                + "c1.goodsIntention like ?13 or " + "c1.legalPerson like ?14 or "
                                + "c1.memberAccount like ?15 or " + "c1.webSite like ?16 or "
                                + "c1.region like ?17 or " + "c1.userSigned like ?18 or "
                                + "c1.customerLevel.name like ?19 or " + "c1.customerStatus.name like ?20 or "
                                + "c1.scale like ?21)";
            }
            Query countQuery = getEntityManager ().createQuery (selectCountSql + fromSql + conditionSql);
            countQuery.setParameter (1, signUser);
            countQuery.setParameter (2, typeCode);
            if (StringUtils.isNotBlank (searchKey))
            {
                countQuery.setParameter (3, keyWord + "%");
                countQuery.setParameter (4, keyWord + "%");
                countQuery.setParameter (5, keyWord + "%");
                countQuery.setParameter (6, keyWord + "%");
                countQuery.setParameter (7, keyWord + "%");
                countQuery.setParameter (8, keyWord + "%");
                countQuery.setParameter (9, keyWord + "%");
                countQuery.setParameter (10, keyWord + "%");
                countQuery.setParameter (11, keyWord + "%");
                countQuery.setParameter (12, keyWord + "%");
                countQuery.setParameter (13, keyWord + "%");
                countQuery.setParameter (14, keyWord + "%");
                countQuery.setParameter (15, keyWord + "%");
                countQuery.setParameter (16, keyWord + "%");
                countQuery.setParameter (17, keyWord + "%");
                countQuery.setParameter (18, keyWord + "%");
                countQuery.setParameter (19, keyWord + "%");
                countQuery.setParameter (20, keyWord + "%");
                countQuery.setParameter (21, keyWord + "%");
            }
            int total = Integer.parseInt (countQuery.getResultList ().get (0).toString ());

            String querySql = "SELECT c ";
            querySql += fromSql;
            querySql += conditionSql;
            String orderBySql = " ORDER BY ";
            querySql += orderBySql;
            
            String sortSqlStr = "c.shortName asc";
            try 
            {
                sortSqlStr = GenericDaoUtils.parseSort (Customer.class, pageable, "c");
            }
            catch (Exception e) 
            {
                // TODO: handle exception
            }
            querySql += sortSqlStr;
            
            Query query = getEntityManager ().createQuery (querySql);
            query.setParameter (1, signUser);
            query.setParameter (2, typeCode);
            if (StringUtils.isNotBlank (searchKey))
            {
                query.setParameter (3, keyWord + "%");
                query.setParameter (4, keyWord + "%");
                query.setParameter (5, keyWord + "%");
                query.setParameter (6, keyWord + "%");
                query.setParameter (7, keyWord + "%");
                query.setParameter (8, keyWord + "%");
                query.setParameter (9, keyWord + "%");
                query.setParameter (10, keyWord + "%");
                query.setParameter (11, keyWord + "%");
                query.setParameter (12, keyWord + "%");
                query.setParameter (13, keyWord + "%");
                query.setParameter (14, keyWord + "%");
                query.setParameter (15, keyWord + "%");
                query.setParameter (16, keyWord + "%");
                query.setParameter (17, keyWord + "%");
                query.setParameter (18, keyWord + "%");
                query.setParameter (19, keyWord + "%");
                query.setParameter (20, keyWord + "%");
                query.setParameter (21, keyWord + "%");
            }

            query.setFirstResult (pageable.getOffset ());
            query.setMaxResults (pageable.getPageSize ());

            @SuppressWarnings ("unchecked")
            List <Customer> customers = query.getResultList ();
            for (Customer c : customers)
            {
                c.getContacts ();
            }
            customerPage = new PageImpl <Customer> (customers, pageable, total);
        }
        else
        {
            String selectCountSql = "SELECT count(c) ";
            String fromSql = "FROM Customer c where ";
            String conditionSql = "c.typeCode = ?1 ";
            if (StringUtils.isNotBlank (searchKey))
            {
                conditionSql += "and c.id IN ";
                conditionSql += "(select c1.id from Customer c1 where " + "c1.shortName like ?2 or "
                                + "c1.zipCode like ?3 or " + "c1.companyName like ?4 or "
                                + "c1.companyIntroduction like ?5 or " + "c1.address like ?6 or "
                                + "c1.email like ?7 or " + "c1.fax like ?8 or " + "c1.fixedPhone like ?9 or "
                                + "c1.customerTrade.name like ?10 or " + "c1.customerSource.name like ?11 or "
                                + "c1.goodsIntention like ?12 or " + "c1.legalPerson like ?13 or "
                                + "c1.memberAccount like ?14 or " + "c1.webSite like ?15 or "
                                + "c1.region like ?16 or " + "c1.userSigned like ?17 or "
                                + "c1.customerLevel.name like ?18 or " + "c1.customerStatus.name like ?19 or "
                                + "c1.scale like ?20)";
            }
            Query countQuery = getEntityManager ().createQuery (selectCountSql + fromSql + conditionSql);
            countQuery.setParameter (1, typeCode);
            if (StringUtils.isNotBlank (searchKey))
            {
                countQuery.setParameter (2, keyWord + "%");
                countQuery.setParameter (3, keyWord + "%");
                countQuery.setParameter (4, keyWord + "%");
                countQuery.setParameter (5, keyWord + "%");
                countQuery.setParameter (6, keyWord + "%");
                countQuery.setParameter (7, keyWord + "%");
                countQuery.setParameter (8, keyWord + "%");
                countQuery.setParameter (9, keyWord + "%");
                countQuery.setParameter (10, keyWord + "%");
                countQuery.setParameter (11, keyWord + "%");
                countQuery.setParameter (12, keyWord + "%");
                countQuery.setParameter (13, keyWord + "%");
                countQuery.setParameter (14, keyWord + "%");
                countQuery.setParameter (15, keyWord + "%");
                countQuery.setParameter (16, keyWord + "%");
                countQuery.setParameter (17, keyWord + "%");
                countQuery.setParameter (18, keyWord + "%");
                countQuery.setParameter (19, keyWord + "%");
                countQuery.setParameter (20, keyWord + "%");
            }
            int total = Integer.parseInt (countQuery.getResultList ().get (0).toString ());

            String querySql = "SELECT c ";
            querySql += fromSql;
            querySql += conditionSql;
            String orderBySql = " ORDER BY ";
            querySql += orderBySql;
            
            String sortSqlStr = "c.shortName asc";
            try 
            {
                sortSqlStr = GenericDaoUtils.parseSort (Customer.class, pageable, "c");
            }
            catch (Exception e) {
                // TODO: handle exception
            }
            querySql += sortSqlStr;

            Query query = getEntityManager ().createQuery (querySql);
            query.setParameter (1, typeCode);
            if (StringUtils.isNotBlank (searchKey))
            {
                query.setParameter (2, keyWord + "%");
                query.setParameter (3, keyWord + "%");
                query.setParameter (4, keyWord + "%");
                query.setParameter (5, keyWord + "%");
                query.setParameter (6, keyWord + "%");
                query.setParameter (7, keyWord + "%");
                query.setParameter (8, keyWord + "%");
                query.setParameter (9, keyWord + "%");
                query.setParameter (10, keyWord + "%");
                query.setParameter (11, keyWord + "%");
                query.setParameter (12, keyWord + "%");
                query.setParameter (13, keyWord + "%");
                query.setParameter (14, keyWord + "%");
                query.setParameter (15, keyWord + "%");
                query.setParameter (16, keyWord + "%");
                query.setParameter (17, keyWord + "%");
                query.setParameter (18, keyWord + "%");
                query.setParameter (19, keyWord + "%");
                query.setParameter (20, keyWord + "%");
            }

            query.setFirstResult (pageable.getOffset ());
            query.setMaxResults (pageable.getPageSize ());

            @SuppressWarnings ("unchecked")
            List <Customer> customers = query.getResultList ();
            for (Customer c : customers)
            {
                c.getContacts ();
            }
            customerPage = new PageImpl <Customer> (customers, pageable, total);
        }
        return customerPage;
    }

    @Override
    public float countUnpayBy (CustomerTypeCode typeCode, String searchKey, long signUser,
                               boolean admin)
    {
        logger.debug ("Find paging customer by fuzzy, searchkey: " + searchKey);
        String keyWord = (null == searchKey ? "" : searchKey.trim ());
        
        float sumUnpay = 0;
        if (!admin && 0 != signUser)
        {
            String selectCountSql = "SELECT sum(c.dealMoney) ";
            String fromSql = "FROM Customer c where ";
            String conditionSql = "c.userSigned = ?1 and c.typeCode = ?2 ";
            if (StringUtils.isNotBlank (searchKey))
            {
                conditionSql += "and c.id IN ";
                conditionSql += "(select c1.id from Customer c1 where " + "c1.shortName like ?3 or "
                                + "c1.zipCode like ?4 or " + "c1.companyName like ?5 or "
                                + "c1.companyIntroduction like ?6 or " + "c1.address like ?7 or "
                                + "c1.email like ?8 or " + "c1.fax like ?9 or " + "c1.fixedPhone like ?10 or "
                                + "c1.customerTrade.name like ?11 or " + "c1.customerSource.name like ?12 or "
                                + "c1.goodsIntention like ?13 or " + "c1.legalPerson like ?14 or "
                                + "c1.memberAccount like ?15 or " + "c1.webSite like ?16 or "
                                + "c1.region like ?17 or " + "c1.userSigned like ?18 or "
                                + "c1.customerLevel.name like ?19 or " + "c1.customerStatus.name like ?20 or "
                                + "c1.scale like ?21)";
            }
            Query countQuery = getEntityManager ().createQuery (selectCountSql + fromSql + conditionSql);
            countQuery.setParameter (1, signUser);
            countQuery.setParameter (2, typeCode);
            if (StringUtils.isNotBlank (searchKey))
            {
                countQuery.setParameter (3, keyWord + "%");
                countQuery.setParameter (4, keyWord + "%");
                countQuery.setParameter (5, keyWord + "%");
                countQuery.setParameter (6, keyWord + "%");
                countQuery.setParameter (7, keyWord + "%");
                countQuery.setParameter (8, keyWord + "%");
                countQuery.setParameter (9, keyWord + "%");
                countQuery.setParameter (10, keyWord + "%");
                countQuery.setParameter (11, keyWord + "%");
                countQuery.setParameter (12, keyWord + "%");
                countQuery.setParameter (13, keyWord + "%");
                countQuery.setParameter (14, keyWord + "%");
                countQuery.setParameter (15, keyWord + "%");
                countQuery.setParameter (16, keyWord + "%");
                countQuery.setParameter (17, keyWord + "%");
                countQuery.setParameter (18, keyWord + "%");
                countQuery.setParameter (19, keyWord + "%");
                countQuery.setParameter (20, keyWord + "%");
                countQuery.setParameter (21, keyWord + "%");
            }
            sumUnpay = Float.parseFloat (countQuery.getResultList ().get (0).toString ());
        }
        else
        {
            String selectCountSql = "SELECT sum(c.dealMoney) ";
            String fromSql = "FROM Customer c where ";
            String conditionSql = "c.typeCode = ?1 ";
            if (StringUtils.isNotBlank (searchKey))
            {
                conditionSql += "and c.id IN ";
                conditionSql += "(select c1.id from Customer c1 where " + "c1.shortName like ?2 or "
                                + "c1.zipCode like ?3 or " + "c1.companyName like ?4 or "
                                + "c1.companyIntroduction like ?5 or " + "c1.address like ?6 or "
                                + "c1.email like ?7 or " + "c1.fax like ?8 or " + "c1.fixedPhone like ?9 or "
                                + "c1.customerTrade.name like ?10 or " + "c1.customerSource.name like ?11 or "
                                + "c1.goodsIntention like ?12 or " + "c1.legalPerson like ?13 or "
                                + "c1.memberAccount like ?14 or " + "c1.webSite like ?15 or "
                                + "c1.region like ?16 or " + "c1.userSigned like ?17 or "
                                + "c1.customerLevel.name like ?18 or " + "c1.customerStatus.name like ?19 or "
                                + "c1.scale like ?20)";
            }
            Query countQuery = getEntityManager ().createQuery (selectCountSql + fromSql + conditionSql);
            countQuery.setParameter (1, typeCode);
            if (StringUtils.isNotBlank (searchKey))
            {
                countQuery.setParameter (2, keyWord + "%");
                countQuery.setParameter (3, keyWord + "%");
                countQuery.setParameter (4, keyWord + "%");
                countQuery.setParameter (5, keyWord + "%");
                countQuery.setParameter (6, keyWord + "%");
                countQuery.setParameter (7, keyWord + "%");
                countQuery.setParameter (8, keyWord + "%");
                countQuery.setParameter (9, keyWord + "%");
                countQuery.setParameter (10, keyWord + "%");
                countQuery.setParameter (11, keyWord + "%");
                countQuery.setParameter (12, keyWord + "%");
                countQuery.setParameter (13, keyWord + "%");
                countQuery.setParameter (14, keyWord + "%");
                countQuery.setParameter (15, keyWord + "%");
                countQuery.setParameter (16, keyWord + "%");
                countQuery.setParameter (17, keyWord + "%");
                countQuery.setParameter (18, keyWord + "%");
                countQuery.setParameter (19, keyWord + "%");
                countQuery.setParameter (20, keyWord + "%");
            }
            sumUnpay = Float.parseFloat (countQuery.getResultList ().get (0).toString ());
        }
        return sumUnpay;
    }

    @Override
    public Page <Customer> findCustomerByFuzzy (Pageable pageable, String searchKey, long signUser, boolean admin)
    {
        String pageableLog = "offSet:" + pageable.getOffset () + ",pageNumber:" + pageable.getPageNumber ()
                             + ",pageSize:" + pageable.getPageSize () + ",sort:" + pageable.getSort ();
        logger.debug ("Find paging customer by fuzzy, pageable: (" + pageableLog + "), searchkey: " + searchKey);
        String keyWord = (null == searchKey ? "" : searchKey.trim ());
        Page <Customer> customerPage;
        if (!admin && 0 != signUser)
        {
            String selectCountSql = "SELECT count(c) ";
            String fromSql = "FROM Customer c where ";
            String conditionSql = "c.userSigned = ?1 ";
            if (StringUtils.isNotBlank (searchKey))
            {
                conditionSql += "and c.id IN ";
                conditionSql += "(select c1.id from Customer c1 where " + "c1.shortName like ?2 or "
                                + "c1.zipCode like ?3 or " + "c1.companyName like ?4 or "
                                + "c1.companyIntroduction like ?5 or " + "c1.address like ?6 or "
                                + "c1.email like ?7 or " + "c1.fax like ?8 or " + "c1.fixedPhone like ?9 or "
                                + "c1.customerTrade.name like ?10 or " + "c1.customerSource.name like ?11 or "
                                + "c1.goodsIntention like ?12 or " + "c1.legalPerson like ?13 or "
                                + "c1.memberAccount like ?14 or " + "c1.webSite like ?15 or "
                                + "c1.region like ?16 or " + "c1.userSigned like ?17 or "
                                + "c1.customerLevel.name like ?18 or " + "c1.customerStatus.name like ?19 or "
                                + "c1.scale like ?20)";
            }
            Query countQuery = getEntityManager ().createQuery (selectCountSql + fromSql + conditionSql);
            countQuery.setParameter (1, signUser);
            if (StringUtils.isNotBlank (searchKey))
            {
                countQuery.setParameter (2, keyWord + "%");
                countQuery.setParameter (3, keyWord + "%");
                countQuery.setParameter (4, keyWord + "%");
                countQuery.setParameter (5, keyWord + "%");
                countQuery.setParameter (6, keyWord + "%");
                countQuery.setParameter (7, keyWord + "%");
                countQuery.setParameter (8, keyWord + "%");
                countQuery.setParameter (9, keyWord + "%");
                countQuery.setParameter (10, keyWord + "%");
                countQuery.setParameter (11, keyWord + "%");
                countQuery.setParameter (12, keyWord + "%");
                countQuery.setParameter (13, keyWord + "%");
                countQuery.setParameter (14, keyWord + "%");
                countQuery.setParameter (15, keyWord + "%");
                countQuery.setParameter (16, keyWord + "%");
                countQuery.setParameter (17, keyWord + "%");
                countQuery.setParameter (18, keyWord + "%");
                countQuery.setParameter (19, keyWord + "%");
                countQuery.setParameter (20, keyWord + "%");
            }
            int total = Integer.parseInt (countQuery.getResultList ().get (0).toString ());

            String querySql = "SELECT c ";
            querySql += fromSql;
            querySql += conditionSql;
            String orderBySql = " ORDER BY ";
            querySql += orderBySql;
            
            String sortSqlStr = "c.shortName asc"; 
            try 
            {
                sortSqlStr = GenericDaoUtils.parseSort (Customer.class, pageable, "c");
            }
            catch (Exception e) {
                // TODO: handle exception
            }
            querySql += sortSqlStr;

            Query query = getEntityManager ().createQuery (querySql);
            query.setParameter (1, signUser);
            if (StringUtils.isNotBlank (searchKey))
            {
                query.setParameter (2, keyWord + "%");
                query.setParameter (3, keyWord + "%");
                query.setParameter (4, keyWord + "%");
                query.setParameter (5, keyWord + "%");
                query.setParameter (6, keyWord + "%");
                query.setParameter (7, keyWord + "%");
                query.setParameter (8, keyWord + "%");
                query.setParameter (9, keyWord + "%");
                query.setParameter (10, keyWord + "%");
                query.setParameter (11, keyWord + "%");
                query.setParameter (12, keyWord + "%");
                query.setParameter (13, keyWord + "%");
                query.setParameter (14, keyWord + "%");
                query.setParameter (15, keyWord + "%");
                query.setParameter (16, keyWord + "%");
                query.setParameter (17, keyWord + "%");
                query.setParameter (18, keyWord + "%");
                query.setParameter (19, keyWord + "%");
                query.setParameter (20, keyWord + "%");
            }

            query.setFirstResult (pageable.getOffset ());
            query.setMaxResults (pageable.getPageSize ());

            @SuppressWarnings ("unchecked")
            List <Customer> customers = query.getResultList ();
            customerPage = new PageImpl <Customer> (customers, pageable, total);
        }
        else
        {
            String selectCountSql = "SELECT count(c) ";
            String fromSql = "FROM Customer c ";
            String conditionSql = "";
            if (StringUtils.isNotBlank (searchKey))
            {
                conditionSql += "where c.id IN ";
                conditionSql += "(select c1.id from Customer c1 where " + "c1.shortName like ?1 or "
                                + "c1.zipCode like ?2 or " + "c1.companyName like ?3 or "
                                + "c1.companyIntroduction like ?4 or " + "c1.address like ?5 or "
                                + "c1.email like ?6 or " + "c1.fax like ?7 or " + "c1.fixedPhone like ?8 or "
                                + "c1.customerTrade.name like ?9 or " + "c1.customerSource.name like ?10 or "
                                + "c1.goodsIntention like ?11 or " + "c1.legalPerson like ?12 or "
                                + "c1.memberAccount like ?13 or " + "c1.webSite like ?14 or "
                                + "c1.region like ?15 or " + "c1.userSigned like ?16 or "
                                + "c1.customerLevel.name like ?17 or " + "c1.customerStatus.name like ?18 or "
                                + "c1.scale like ?19)";
            }
            Query countQuery = getEntityManager ().createQuery (selectCountSql + fromSql + conditionSql);
            if (StringUtils.isNotBlank (searchKey))
            {
                countQuery.setParameter (1, keyWord + "%");
                countQuery.setParameter (2, keyWord + "%");
                countQuery.setParameter (3, keyWord + "%");
                countQuery.setParameter (4, keyWord + "%");
                countQuery.setParameter (5, keyWord + "%");
                countQuery.setParameter (6, keyWord + "%");
                countQuery.setParameter (7, keyWord + "%");
                countQuery.setParameter (8, keyWord + "%");
                countQuery.setParameter (9, keyWord + "%");
                countQuery.setParameter (10, keyWord + "%");
                countQuery.setParameter (11, keyWord + "%");
                countQuery.setParameter (12, keyWord + "%");
                countQuery.setParameter (13, keyWord + "%");
                countQuery.setParameter (14, keyWord + "%");
                countQuery.setParameter (15, keyWord + "%");
                countQuery.setParameter (16, keyWord + "%");
                countQuery.setParameter (17, keyWord + "%");
                countQuery.setParameter (18, keyWord + "%");
                countQuery.setParameter (19, keyWord + "%");
            }
            int total = Integer.parseInt (countQuery.getResultList ().get (0).toString ());

            String querySql = "SELECT c ";
            querySql += fromSql;
            querySql += conditionSql;
            String orderBySql = " ORDER BY ";
            querySql += orderBySql;
            
            String sortSqlStr = "c.shortName asc";
            try 
            {
                sortSqlStr = GenericDaoUtils.parseSort (Customer.class, pageable, "c");
            }
            catch (Exception e) {
                // TODO: handle exception
            }
            querySql += sortSqlStr;

            Query query = getEntityManager ().createQuery (querySql);
            if (StringUtils.isNotBlank (searchKey))
            {
                query.setParameter (1, keyWord + "%");
                query.setParameter (2, keyWord + "%");
                query.setParameter (3, keyWord + "%");
                query.setParameter (4, keyWord + "%");
                query.setParameter (5, keyWord + "%");
                query.setParameter (6, keyWord + "%");
                query.setParameter (7, keyWord + "%");
                query.setParameter (8, keyWord + "%");
                query.setParameter (9, keyWord + "%");
                query.setParameter (10, keyWord + "%");
                query.setParameter (11, keyWord + "%");
                query.setParameter (12, keyWord + "%");
                query.setParameter (13, keyWord + "%");
                query.setParameter (14, keyWord + "%");
                query.setParameter (15, keyWord + "%");
                query.setParameter (16, keyWord + "%");
                query.setParameter (17, keyWord + "%");
                query.setParameter (18, keyWord + "%");
                query.setParameter (19, keyWord + "%");
            }

            query.setFirstResult (pageable.getOffset ());
            query.setMaxResults (pageable.getPageSize ());

            @SuppressWarnings ("unchecked")
            List <Customer> customers = query.getResultList ();
            customerPage = new PageImpl <Customer> (customers, pageable, total);
        }
        return customerPage;
    }

    @Override
    @SuppressWarnings ("unchecked")
    public List <Customer> findAllCustomer (CustomerTypeCode typeCode, long signUser, boolean admin)
    {
        List <Customer> customers = new ArrayList <Customer> ();
        String nsql = "select c from Customer c ";
        if (null != typeCode)
        {
            nsql += "where c.typeCode = ?1 ";
            if (!admin && 0 != signUser)
            {
                nsql += "and c.userSigned = ?2 ";
                nsql += "order by c.shortName asc";
                Query query = getEntityManager ().createQuery (nsql, Customer.class);
                query.setParameter (1, typeCode);
                query.setParameter (2, signUser);
                customers = query.getResultList ();
            }
            else
            {
                nsql += "order by c.shortName asc";
                Query query = getEntityManager ().createQuery (nsql, Customer.class);
                query.setParameter (1, typeCode);
                customers = query.getResultList ();
            }
        }
        else
        {
            if (!admin && 0 != signUser)
            {
                nsql += "where c.userSigned = ?1 ";
                nsql += "order by c.shortName asc";
                Query query = getEntityManager ().createQuery (nsql, Customer.class);
                query.setParameter (1, signUser);
                customers = query.getResultList ();
            }
            else
            {
                nsql += "order by c.shortName asc";
                Query query = getEntityManager ().createQuery (nsql, Customer.class);
                customers = query.getResultList ();
            }
        }

        for (Customer c : customers)
        {
            c.getContacts ();
        }
        return customers;
    }

    /**
     * Native sql query speed is lower than JPQL
     * 
     * @param typeCode
     * @param signUser
     * @param admin
     * @return
     */
    @SuppressWarnings ("unchecked")
    public List <Customer> getCustomer (CustomerTypeCode typeCode, long signUser, boolean admin)
    {
        List <Customer> customers = new ArrayList <Customer> ();
        int type = CustomerTypeCode.CUSTOMER == typeCode ? 0 : 1;
        String nsql = "select * from pc_customer c where c.typeCode = ?1 ";
        if (!admin && 0 != signUser)
        {
            nsql += "and c.userSigned = ?2 ";
            nsql += "order by convert(c.shortName USING gbk) COLLATE gbk_chinese_ci asc";
            Query query = getEntityManager ().createNativeQuery (nsql, Customer.class);
            query.setParameter (1, type);
            query.setParameter (2, signUser);
            customers = query.getResultList ();
        }
        else
        {
            nsql += "order by convert(c.shortName USING gbk) COLLATE gbk_chinese_ci asc";
            Query query = getEntityManager ().createNativeQuery (nsql, Customer.class);
            query.setParameter (1, type);
            customers = query.getResultList ();
        }

        for (Customer c : customers)
        {
            c.getContacts ();
        }
        return customers;
    }

    @Override
    public boolean addDealMoney (float unpayMoney, long id)
    {
        Customer customer = getEntityManager ().find (Customer.class, id);
        customer.setDealMoney (customer.getDealMoney () + unpayMoney);
        try
        {
            getEntityManager ().merge (customer);
            return true;
        }
        catch (Exception e)
        {
            logger.warn ("Add deal money for customer fail, " + e.getMessage ());
            return false;
        }
    }

    @SuppressWarnings ("unchecked")
    @Override
    public List<Customer> findByShortNameLike(String name, CustomerTypeCode code, long signUser, boolean admin)
    {
        logger.debug ("findByShortNameLike: " + name);
        List <Customer> customerList = new ArrayList <Customer> ();
        String searchKey = (null == name ? "" : name.trim ());
        int typeCode = CustomerTypeCode.CUSTOMER.equals (code) ? 0 : 1;
        if(null != code) 
        {
            if (!admin && 0 != signUser)
            {
                String sql = "select * from pc_customer c where c.typeCode = ?1 and c.userSigned = ?2 and c.shortName like ?3 order by convert(c.shortName USING gbk) COLLATE gbk_chinese_ci asc";
                Query query = getEntityManager ().createNativeQuery (sql, Customer.class);
                query.setParameter (1, typeCode);
                query.setParameter (2, signUser);
                query.setParameter (3, "%" + searchKey + "%");
                customerList = query.getResultList ();
            }
            else 
            {
                String sql = "select * from pc_customer c where c.typeCode = ?1 and c.shortName like ?2 order by convert(c.shortName USING gbk) COLLATE gbk_chinese_ci asc";
                Query query = getEntityManager ().createNativeQuery (sql, Customer.class);
                query.setParameter (1, typeCode);
                query.setParameter (2, "%" + searchKey + "%");
                customerList = query.getResultList ();
            }
        }
        else 
        {
            if (!admin && 0 != signUser)
            {
                String sql = "select * from pc_customer c where c.userSigned = ?1 and c.shortName like ?2 order by convert(c.shortName USING gbk) COLLATE gbk_chinese_ci asc";
                Query query = getEntityManager ().createNativeQuery (sql, Customer.class);
                query.setParameter (1, signUser);
                query.setParameter (2, "%" + searchKey + "%");
                customerList = query.getResultList ();
            }
            else 
            {
                String sql = "select * from pc_customer c where c.shortName like ?1 order by convert(c.shortName USING gbk) COLLATE gbk_chinese_ci asc";
                Query query = getEntityManager ().createNativeQuery (sql, Customer.class);
                query.setParameter (1, "%" + searchKey + "%");
                customerList = query.getResultList ();
            }
        }
        
        for (Customer c : customerList)
        {
            c.getContacts ();
        }
        return customerList;
    }
}
